topic Re: Qlikview Pivot and flag in App Development

Qlikview Pivot and flag

fishing_weights — Fri, 15 Nov 2024 21:27:36 GMT

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.

Re: Qlikview Pivot and flag

marcus_sommer — Wed, 23 Aug 2023 17:01:36 GMT

You may try an expression like:

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

Re: Qlikview Pivot and flag

fishing_weights — Thu, 24 Aug 2023 14:07:36 GMT

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

Re: Qlikview Pivot and flag

marcus_sommer — Thu, 24 Aug 2023 14:27:17 GMT

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

Re: Qlikview Pivot and flag

fishing_weights — Thu, 24 Aug 2023 16:35:38 GMT

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

Re: Qlikview Pivot and flag

marcus_sommer — Fri, 25 Aug 2023 07:46:53 GMT

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.

Re: Qlikview Pivot and flag

fishing_weights — Thu, 31 Aug 2023 05:57:21 GMT

@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: