Qlikview Pivot and flag

[fishing_weights]

Original Table:

LVL1	LVL2	LVL3	DATE	ITEM
1	1A	1A1	01 JAN 23	APPLE
1	1A	1A1	01 JAN 23	PEAR
1	1A	1A1	01 FEB 23	APPLE
1	1A	1A1	01 FEB 23	APPLE

I need to pivot it and aggregate it to look like this:

LVL1	LVL2	LVL3	1JAN23	1FEB23	#of times Apple appear in the dates
1	1A	1A1	=Count(APPLE)	=Count(APPLE)	2

Note that I'm dealing with a lot more LVLs and Dates

I'm able to Pivot the date field by dragging in the chart with the blue arrow. But how do I get the last field where I want to show the number of times something occur with each date field?

thanks in advance.

[marcus_sommer]

You may try an expression like:

count({< ITEM = {'APPLE'}>} distinct DATE&ITEM)

[fishing_weights]

This doesn't work. it just returns a value of 0

[marcus_sommer]

In regard to your shown example the suggestion should work. What's different to it within your real case?

[fishing_weights]

Hi @marcus_sommer 

Please see attached. the xlsx is the data i'm loading and in the qvw the object box I've written where I am stuck at.

Hope this makes things clearer. 

Edit: The problem statement would be:

1. For each ID there are items tagged to them

2. For each item it is labeled "Free" or "Expensive"

3. Over a rolling 12 months Period

4. Show the % of Expensive items over the total items of each ID for each date

5. Count the number of times point 4 is over by 10% and display at the end of the table

[marcus_sommer · Accepted solution]

Your real case differs a bit from your origin description because it includes a condition and not for the exists of data else for a the result of a calculation.

In each case you will need at least two different expressions - one for the rate and one for the count of certain rates. Because of the fact that each expression enforces an own column for a dimension-value you couldn't simply add two expressions else they need to be combined and and you need a separate column to which these results could be plotted. For this you may add a separate dimension-value within your date-dimension or you could enable the partial sums for the date-dimension and then querying the secondarydimensionality() to branch appropriate. For example with something like this:

=
if(secondarydimensionality() > 0,

Round(Count(DISTINCT
{<
STATUS= {'Expensive'},
date={">=$(=Date(vDate_L13))<=$(=Date(vDate))"}
>}
ITEM)
/
Count(DISTINCT
{<
date={">=$(=Date(vDate_L13))<=$(=Date(vDate))"}
>}
ITEM)
,0.001),
sum(
{<
date={">=$(=Date(vDate_L13))<=$(=Date(vDate))"}
>}
aggr(if(
Round(Count(DISTINCT
{<
STATUS= {'Expensive'},
date={">=$(=Date(vDate_L13))<=$(=Date(vDate))"}
>}
ITEM)
/
Count(DISTINCT
{<
date={">=$(=Date(vDate_L13))<=$(=Date(vDate))"}
>}
ITEM)
,0.001) > 0.1, 1), ID, date)))

An alternatively could be to skip the date-dimension and applying 13 expressions - 12 for your rolling months with appropriate date-filters in the expressions as well as within the labels und within the 13. expression you applying the count of TRUE which will in each case require an aggr() logic.

[fishing_weights]

@marcus_sommer  Thanks! I do not fully understand but it works!

I had to Group the two expressions together and then do a partial sum of the date

This gives me the result: